
package com.scalar.chiptrack.adaptor.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;

import com.scalar.ScalarException;
import com.scalar.chiptrack.adaptor.Adaptor;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.utils.SqlQueryMappings;


public final class AdaptorRunInfo {
    
    public AdaptorRunInfo()
   {

   }

   public static ArrayList getAdaptorRunInfoList() throws ScalarException
   {
       ArrayList runInfoList = new ArrayList();
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rset = null;
       Adaptor adaptorinfo = null;
       try {
           conn = DataSource.getConnection();

           pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.getAdaptorLastRuningInfo"));
           rset = pstmt.executeQuery();

         while(rset.next())
         {
               adaptorinfo = new Adaptor();
               adaptorinfo.setAdaptorName(rset.getString("adaptor_name")) ;
               adaptorinfo.setAdaptorRunTime(rset.getString("updated_time"));
               adaptorinfo.setFileName(rset.getString("Adaptor_File_Name") );
               adaptorinfo.setUploadDate(rset.getString("Upload_Date") );
               runInfoList.add(adaptorinfo);
         }
       }
       catch (Exception sqle) {
           sqle.printStackTrace();
           throw new ScalarException ("Could not get Adaptor Last Running Time from database", sqle);
       }

       finally
       {
           if (rset != null)
           {
               try { rset.close(); } catch (SQLException ignore){}
           }
           if (pstmt != null)
           {
               try { pstmt.close(); } catch (SQLException ignore){}
           }
           if (conn != null)
           {
               try { conn.close(); } catch (SQLException ignore){}
           }
       }

       return runInfoList;
   }

    public static String getPreviousUpdatedFileList(String adaptorName) throws ScalarException
   {
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rset = null;
       String fileName = null;
       try 
       {
           conn = DataSource.getConnection();

           pstmt = conn.prepareStatement ("select Adaptor_Name, Updated_Time, Adaptor_File_Name, Upload_Date from FN_GET_ADAPTOR_RUNINFO_STATUS() WHERE Adaptor_Name= ? ");
           pstmt.setString(1, adaptorName);
           rset = pstmt.executeQuery();

         if(rset.next())
         {
              fileName = rset.getString("Adaptor_File_Name");
         }
       }
       catch (Exception sqle) {
           sqle.printStackTrace();
           throw new ScalarException ("Could not get Adaptor Last Running Time from database", sqle);
       }

       finally
       {
           if (rset != null)
           {
               try { rset.close(); } catch (SQLException ignore){}
           }
           if (pstmt != null)
           {
               try { pstmt.close(); } catch (SQLException ignore){}
           }
           if (conn != null)
           {
               try { conn.close(); } catch (SQLException ignore){}
           }
       }

       return fileName;
   }

   public static String getAdaptorLastParsedFileDate(String adaptorType ) throws ScalarException
   {
       String fileDate         = null;
       Connection conn         = null;
       PreparedStatement pstmt = null;
       ResultSet rset          = null;

       try
       {
           conn = DataSource.getConnection();

           pstmt = conn.prepareStatement ("SELECT MAX_FILE_DATE FROM ADAPTOR_RUNINFO_TABLE WHERE ADAPTOR_NAME = ? AND ADAPTOR_STATUS ='SUCCESSFUL' and UPDATED_TIME = (SELECT MAX(UPDATED_TIME) FROM ADAPTOR_RUNINFO_TABLE WHERE ADAPTOR_TYPE = 'ADAPTOR' AND ADAPTOR_STATUS ='SUCCESSFUL' AND ADAPTOR_NAME = ? )");
           pstmt.setString( 1, adaptorType );
           pstmt.setString( 2, adaptorType );
           rset = pstmt.executeQuery();

           while( rset.next() )
           {
               fileDate = rset.getString("MAX_FILE_DATE");
           }
       }
       catch (Exception sqle) {
           sqle.printStackTrace();
           throw new ScalarException ("Could not get Adaptor Last Parsed File Date from database", sqle);
       }

       finally
       {
           if ( rset != null )
           {
               try { rset.close(); } catch (SQLException ignore){}
           }
           if ( pstmt != null )
           {
               try { pstmt.close(); } catch (SQLException ignore){}
           }
           if ( conn != null )
           {
               try { conn.close(); } catch (SQLException ignore){}
           }
       }

       return fileDate;
   }


   public static int saveAdaptorLastUpdatedStatus ( String adaptorType, String adaptorName, String status, int fileId ) throws ScalarException
    {
       Connection conn         = null;
       PreparedStatement pstmt = null;
       ResultSet         rs    = null;
       boolean success = false;
       int adaptorId = -1;

       try
       {
           conn = DataSource.getConnection();
           // Store Adaptor Information
           pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.saveAdaptorRunInfoStatus"));
           pstmt.setString (1, adaptorType);
           pstmt.setString (2, adaptorName);
           pstmt.setString (3, status);
           pstmt.setInt    (4, fileId);

           pstmt.execute();
           pstmt.close();
           pstmt = null;

            pstmt = conn.prepareStatement(SqlQueryMappings.get ("adaptor.getAdaptorId"));
            rs = pstmt.executeQuery();

            if ( rs.next() )
            {
               adaptorId = rs.getInt("Adaptor_Id");
            }
            success = true;
       }
       catch (Exception sqle)
       {
           sqle.printStackTrace();
           throw new ScalarException ("Could not save Adaptor Last Run Information into database", sqle);
       }
       finally
       {
           // Commit/Rollback here it selef -- if its not in a manual transaction
                try
                {
                   if (conn != null)
                   {
                       if (success == true)
                       {
                           conn.commit();
                       }
                       else
                       {
                           conn.rollback();
                       }
                   }
               }
               catch (SQLException ignore)
               {

               }
           if ( rs != null )
           {
                try { rs.close(); } catch (SQLException ignore){}
           }
           if (pstmt != null)
           {
               try { pstmt.close(); } catch (SQLException ignore){}
           }
           if (conn != null)
           {
               try { conn.close(); } catch (SQLException ignore){}
           }
       }
      return  adaptorId;
   }

    public static void updatedAdaptorStatus ( String status, int adaptorId ) throws ScalarException
    {
       Connection conn = null;
       PreparedStatement pstmt = null;
       boolean success = false;

       try
       {
           conn = DataSource.getConnection();
           // Store Adaptor Information
           pstmt = conn.prepareStatement ("update Adaptor_RunInfo_Table SET Adaptor_Status = ? WHERE Adaptor_Id = ?");
           pstmt.setString (1, status);
           pstmt.setInt (2, adaptorId);

           pstmt.executeUpdate();
           success = true;
       }
       catch (Exception sqle)
       {
           sqle.printStackTrace();
           throw new ScalarException ("Could not update Adaptor Last Run Information into database", sqle);
       }
       finally
       {
           // Commit/Rollback here it selef -- if its not in a manual transaction
                try
                {
                   if (conn != null)
                   {
                       if (success == true)
                       {
                           conn.commit();
                       }
                       else
                       {
                           conn.rollback();
                       }
                   }
               }
               catch (SQLException ignore)
               {

               }

           if (pstmt != null)
           {
               try { pstmt.close(); } catch (SQLException ignore){}
           }
           if (conn != null)
           {
               try { conn.close(); } catch (SQLException ignore){}
           }
       }
   }
    public static void updateLastRunFileDate ( int adaptorId , String fileDate ) throws ScalarException
    {
       Connection conn = null;
       PreparedStatement pstmt = null;
       boolean success = false;

       try
       {
           conn = DataSource.getConnection();
           // Store Adaptor Information
           pstmt = conn.prepareStatement ("update Adaptor_RunInfo_Table SET MAX_FILE_DATE = ISNULL(MAX_FILE_DATE, ? ) WHERE Adaptor_Id = ?");
           pstmt.setString (1, fileDate);
           pstmt.setInt (2, adaptorId);

           pstmt.executeUpdate();
           success = true;
       }
       catch (Exception sqle)
       {
           sqle.printStackTrace();
           throw new ScalarException ("Could not update Adaptor max file date Information into database", sqle);
       }
       finally
       {
           // Commit/Rollback here it selef -- if its not in a manual transaction
                try
                {
                   if (conn != null)
                   {
                       if (success == true)
                       {
                           conn.commit();
                       }
                       else
                       {
                           conn.rollback();
                       }
                   }
               }
               catch (SQLException ignore)
               {

               }

           if (pstmt != null)
           {
               try { pstmt.close(); } catch (SQLException ignore){}
           }
           if (conn != null)
           {
               try { conn.close(); } catch (SQLException ignore){}
           }
       }
   }
   public static void saveAdaptorFileInfo ( int adaptorId, String fileName, String fileType, String uploadDate ) throws ScalarException
    {
       Connection conn         = null;
       PreparedStatement pstmt = null;
       boolean success = false;

       try
       {
           conn = DataSource.getConnection();
           // Store Adaptor File Information
           pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.saveAdaptorFileInfoStatus"));
           pstmt.setInt (1, adaptorId);
           pstmt.setString (2, fileName);
           pstmt.setString (3, fileType);
           pstmt.setString (4, uploadDate);

           pstmt.execute();
           pstmt.close();
           pstmt = null;

           success = true;
       }
       catch (Exception sqle)
       {
           sqle.printStackTrace();
           throw new ScalarException ("Could not save Adaptor Last Run Information into database", sqle);
       }
       finally
       {
           // Commit/Rollback here it selef -- if its not in a manual transaction
                try
                {
                   if (conn != null)
                   {
                       if (success == true)
                       {
                           conn.commit();
                       }
                       else
                       {
                           conn.rollback();
                       }
                   }
               }
               catch (SQLException ignore)
               {

               }
            if (pstmt != null)
           {
               try { pstmt.close(); } catch (SQLException ignore){}
           }
           if (conn != null)
           {
               try { conn.close(); } catch (SQLException ignore){}
           }
       }
   }

   public static int getLastRunAdaptorId ( String adaptorName, String adaptorType) throws ScalarException
    {
       Connection conn         = null;
       PreparedStatement pstmt = null;
       ResultSet         rs    = null;
       boolean success = false;
       int adaptorId = -1;

       try
       {
           conn = DataSource.getConnection();

           pstmt = conn.prepareStatement ("SELECT ADAPTOR_ID FROM ADAPTOR_RUNINFO_TABLE ART WHERE ADAPTOR_NAME = ? AND UPDATED_TIME = (SELECT MAX(UPDATED_TIME) FROM ADAPTOR_RUNINFO_TABLE ARI \n" +
                   "WHERE ART.ADAPTOR_NAME = ARI.ADAPTOR_NAME AND ADAPTOR_TYPE = ? ) ");
           pstmt.setString (1, adaptorName);
           pstmt.setString (2, adaptorType);

           rs = pstmt.executeQuery();

            if ( rs.next() )
            {
               adaptorId = rs.getInt("ADAPTOR_ID");
            }
            success = true;
       }
       catch (Exception sqle)
       {
           sqle.printStackTrace();
           throw new ScalarException ("Could not Get Last Run Adaptor id from database", sqle);
       }
       finally
       {
           // Commit/Rollback here it selef -- if its not in a manual transaction
                try
                {
                   if (conn != null)
                   {
                       if (success == true)
                       {
                           conn.commit();
                       }
                       else
                       {
                           conn.rollback();
                       }
                   }
               }
               catch (SQLException ignore)
               {

               }
           if ( rs != null )
           {
                try { rs.close(); } catch (SQLException ignore){}
           }
           if (pstmt != null)
           {
               try { pstmt.close(); } catch (SQLException ignore){}
           }
           if (conn != null)
           {
               try { conn.close(); } catch (SQLException ignore){}
           }
       }
      return  adaptorId;
   }
}